package net.wang.staffing_system.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 *
 */
public class ExcelUtils {

    public static final String EXCEL_TABLE_TITLE = "tableTitle";
    public static final String EXCEL_TABLE_HEAD = "tableHead";
    public static final String EXCEL_TABLE_BODY = "tableBody";
    public static final String EXCEL_ROW_NUMBER = "tableTotalRow";

    public static Map<String, Object> excelToMap(File file) throws IOException {
        Workbook workbook = getWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);
        int totalRow = sheet.getLastRowNum();
        if (totalRow < 3) throw new IOException("The table must be more than three lines");
        List<Object> tableHead = getTableHead(sheet);
        Map<String, Object> table = new HashMap<>();
        table.put(EXCEL_TABLE_TITLE, getTitle(sheet));
        table.put(EXCEL_TABLE_HEAD, tableHead);
        table.put(EXCEL_TABLE_BODY, getTableContent(sheet, tableHead));
        table.put(EXCEL_ROW_NUMBER, totalRow);
        return table;
    }

    private static Object getTitle(Sheet sheet) {
        return getCellValue(sheet.getRow(0).getCell(0));
    }

    private static List<Object> getTableHead(Sheet sheet) {
        List<Object> list = new LinkedList<>();
        for (int i = 0; i < sheet.getRow(1).getLastCellNum(); i++) {
            list.add(getCellValue(sheet.getRow(1).getCell(i)));
        }
        return list;
    }

    private static List<Map<Object, Object>> getTableContent(Sheet sheet, List<Object> tableHead) throws IOException {
        List<Map<Object, Object>> tableContent = new LinkedList<>();
        for (int i = 2; i < sheet.getLastRowNum(); i++) {
            Row sheetRow = sheet.getRow(i);
            short totalCell = sheetRow.getLastCellNum();
            Map<Object, Object> map = new HashMap<>();
            for (int j = sheetRow.getFirstCellNum(); j < totalCell; j++) {
                map.put(tableHead.get(j), getCellValue(sheetRow.getCell(j)));
            }
            tableContent.add(map);
        }
        return tableContent;
    }


    private static Workbook getWorkbook(File file) throws IOException {

        if (file == null) throw new IOException("excel must not be null");


        boolean isXls = file.getName().endsWith("xls");
        boolean isXlsx = file.getName().endsWith("xlsx");
        FileInputStream fileInputStream = new FileInputStream(file);

        Workbook workbook = null;
        if (isXls) {
            workbook = new HSSFWorkbook(fileInputStream);
        }
        if (isXlsx) {
            workbook = new XSSFWorkbook(fileInputStream);
        }

        if (workbook == null) throw new IOException("file name error");

        return workbook;
    }

    public static Map<String, Object> csvToJSON(File file) throws IOException {
        String title = "";
        List<String> head = new LinkedList<>();
        List<Map<String, Object>> body = new LinkedList<>();
        BufferedReader bufferedReader = null;
        Map<String, Object> table = new HashMap<>();
        try {
            bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF-8"));
            String line;
            int rowNumber = 0;
            while ((line = bufferedReader.readLine()) != null) {
                List<String> rowList = convertCSVRowStringToList(line, true);
                if (rowNumber == 0) {
                    title = convertCSVRowStringToList(line, false).get(0);
                } else if (rowNumber == 1) {
                    head = rowList;
                } else if (rowNumber >= 2) {
                    Map<String, Object> rowMap = new HashMap<>();
                    for (int i = 0; i < rowList.size(); i++) {
                        rowMap.put(head.get(i), rowList.get(i));
                    }
                    body.add(rowMap);

                }
                rowNumber++;
            }
            table.put(EXCEL_TABLE_TITLE, title);
            table.put(EXCEL_TABLE_HEAD, head);
            table.put(EXCEL_TABLE_BODY, body);
            table.put(EXCEL_ROW_NUMBER, rowNumber);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (bufferedReader != null) {
                bufferedReader.close();
            }
        }
        return table;
    }

    private static List<String> convertCSVRowStringToList(String line, boolean preserveEmptyValue) {
        String[] strings = line.split(",");
        List<String> list = new LinkedList<>();
        for (int i = 0; i < strings.length; i++) {
            String string = strings[i];
            if (preserveEmptyValue) {
                list.add(string == null ? "" : string);
            } else {
                if (string != null && string.trim().length() > 0) {
                    list.add(string == null ? "" : string);
                }
            }
        }
        return list;
    }

    private static Object getCellValue(Cell cell) {

        Object cellValue;

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cellValue = cell.getStringCellValue();
                }
                break;
            case Cell.CELL_TYPE_STRING:
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                cellValue = cell.getCellFormula().toString();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }
}
